package com.ljh.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ljh.pojo.Song;
import com.ljh.utils.Result;
import com.ljh.vo.SexVo;
import com.ljh.vo.SongListVo;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface SongMapper extends BaseMapper<Song> {

    /**
     * 查找全部歌曲
     * @return
     */
    @Select("select * from song")
    public List<Song> findAllSongs();

    /**
     * 查找歌曲通过随机函数
     * @param num
     * @return
     */
    @Select("SELECT * from song order by RAND() limit #{num};")
    public List<Song> findNine(Integer num);

    /**
     * 歌曲总数
     * @return
     */
    @Select("select count(*) from song")
    public Integer findCount();

    /**
     * 通过id查找
     * @param id
     * @return
     */
    @Select("select * from song where collId = #{id}")
    public List<Song> findSongsByCollId(Integer id);

    /**
     * 通过集合Id来查找歌曲
     * @param id
     * @return
     */
    @Select("select songId,singerName,songIntroduce,songName,songUrl from song where collId = #{id}")
    public List<SongListVo> findSongListVo(Integer id);

    /**
     * 通过歌曲id来查找歌曲
     * @param id
     * @return
     */
    @Select("select * from song where songId = #{id}")
    public Song findSongById(Integer id);

    /**
     * 通过歌曲风格查询
     * @param style
     * @return
     */
    @Select("select songId,singerName,songPhoto,songName,songUrl from song where songStyle = #{style} order by RAND() limit 20")
    public List<SongListVo> findSongListByStyle(String style);


    /**
     * 更新播放次数
     * @param num
     * @param id
     */
    @Update("update song set songNum = #{num} where songId = #{id}")
    public void updateSongNum(Integer num,Integer id);

    /**
     * 最热歌曲
     * @return
     */
    @Select("select * from song order by songNum desc limit 15")
    public List<Song> findHost15();

    /**
     * 最新歌曲
     * @return
     */
    @Select("select * from song ORDER BY createTime desc limit 15")
    public List<Song> findNew15();

    /**
     * 搜索函数
     * @param str
     * @return
     */
    @Select("SELECT songId,songName,singerName from song where songName LIKE #{str} or singerName LIKE #{str} or songIntroduce LIKE #{str} or songStyle like #{str}")
    public List<Song> findMoHU(String str);

    /**
     *
     * @param style
     * @return
     */
    @Select("select songId,singerName,songPhoto,songName,songUrl from song where songStyle = #{style} order by RAND() limit 3")
    public List<SongListVo> findSongListByStyle2(String style);

    /**
     * 通过collid来删除音乐
     * @param collId
     */
    @Delete("delete from song where collId = #{collId}")
    public void deleteUserByCollid(Integer collId);

    /**
     * 通过songId来删除关系表中的关系
     * @param songId
     */
    @Delete("delete from list_song where songId = #{songId}")
    public void deleteListSongBySongId(Integer songId);

    /**
     * 通过songId来删除歌曲
     * @param songId
     */
    @Delete("delete from song where songId = #{songId}")
    public void deleteSongById(Integer songId);

    /**
     * 通过songId来查找歌曲
     * @param songId
     * @return
     */
    @Select("select * from song where songId = #{songId}")
    public List<Song> findSongBySongId(Integer songId);

    /**
     * 通过songId来修改照片
     * @param songPhoto
     * @param songId
     */
    @Update("update song set songPhoto = #{songPhoto} where songId = #{songId}")
    public void UpdatePersonPhoto(String songPhoto,Integer songId);

    /**
     * 更新图片
     * @param songUrl
     * @param songId
     */
    @Update("update song set songUrl = #{songUrl} where songId = #{songId}")
    public void updateSong(String songUrl,Integer songId);

    @Insert("insert into song (singerName,singerId,songName,songStyle) values (#{singerName},#{singerId},#{songName},#{songStyle})")
    public void insertSong(String singerName,Integer singerId,String songName,String songStyle);

    /**
     * 查出没有加入专辑的歌曲
     */
    @Select("select * from song where collId is null ")
    public List<Song> findSongByCollIdNull();

    @Update("update song set songIntroduce = #{collName},collId = #{collId},songPhoto = #{collPhoto} where songId = #{songId}")
    public void insertColl(Integer collId, String collName, Integer songId,String collPhoto);

    @Select("SELECT songStyle as name,count(*) as value from song GROUP BY songStyle")
    public List<SexVo> findSongStyle();

    @Select("SELECT singerName as name,sum(songNum) as value from song group by singerName ORDER BY value desc limit 10")
    public List<SexVo> findSingerHot();

    @Select("select songName as name ,songNum as value from song ORDER BY songNum desc limit 10")
    public List<SexVo> findHotSong();
}
